﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace Financeiro.Dados
{
    public class RegraCotaDAO
    {

        private static string connectionString = ConfigurationManager.ConnectionStrings["ERP_FinanceiroConnectionString"].ConnectionString;

        public static bool mudarRegra(string campo, double valor, string setor)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            string sqlUpd = "UPDATE RegraCota SET " + campo + "=@valor WHERE setor=@setor";
            bool hasSuccess = false;
            try
            {
                conn.Open();
                SqlCommand cmdUpd = new SqlCommand(sqlUpd, conn);
                cmdUpd.Parameters.Add("@setor", SqlDbType.VarChar);
                cmdUpd.Parameters["@setor"].Value = setor;
                cmdUpd.Parameters.Add("@valor", SqlDbType.Float);
                cmdUpd.Parameters["@valor"].Value = valor;

                if (cmdUpd.ExecuteNonQuery() != 0)
                    hasSuccess = true;

                cmdUpd.Dispose();
                cmdUpd = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return hasSuccess;
        }

        public static DataRow obterRegra(string setor)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            string sqlSel = "SELECT * FROM RegraCota WHERE (setor=@setor)";
            DataRow dr;
            try
            {
                conn.Open();
                SqlCommand cmdSel = new SqlCommand(sqlSel, conn);
                cmdSel.Parameters.Add("@setor", SqlDbType.VarChar);
                cmdSel.Parameters["@setor"].Value = setor;

                SqlDataAdapter da = new SqlDataAdapter(cmdSel);
                DataSet ds = new DataSet();
                da.Fill(ds, "RegraCota");
                dr = ds.Tables["RegraCota"].Rows[0];

                cmdSel.Dispose();
                cmdSel = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return dr;
        }

        public static DataTable obterRegras()
        {
            SqlConnection conn = new SqlConnection(connectionString);

            string sqlSel = "SELECT * FROM RegraCota";
            DataTable dt;
            try
            {
                conn.Open();
                SqlCommand cmdSel = new SqlCommand(sqlSel, conn);

                SqlDataAdapter da = new SqlDataAdapter(cmdSel);
                DataSet ds = new DataSet();
                da.Fill(ds, "RegraCota");
                dt = ds.Tables["RegraCota"];

                cmdSel.Dispose();
                cmdSel = null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

            return dt;
        }

    }
}
